import tkinter as tk
from tkinter import filedialog, messagebox
import os
import polars as pl
from util.db import SQLite
import pygame.mixer as mixer # 使用别名避免冲突
import threading

class InvoiceVerificationApp(tk.Tk):
    def __init__(self, db_path):
        super().__init__()
        self.title("海外原件唛头复核")
        self.geometry("800x600")

        self.db = SQLite(db_path)
        self.current_bill_no = tk.StringVar()
        self.current_box_list_no_input = tk.StringVar()
        self.delete_bill_no_input = tk.StringVar() # 新增：用于删除单据号的文本框变量

        self._create_widgets()
        self._load_audio_files()

    def _load_audio_files(self):
        # 音频文件路径已在用户端修改，这里直接使用
        self.battle_sound_path = r"\\10.146.38.191\最新-备件验收\海外原件复核程序\battle.mp3"
        self.fail_sound_path = r"\\10.146.38.191\最新-备件验收\海外原件复核程序\fail.mp3"
        self.battle_sound_obj = None
        self.fail_sound_obj = None

        try:
            # 初始化pygame mixer
            if not mixer.get_init():
                mixer.init() # 默认参数通常足够

            if os.path.exists(self.battle_sound_path):
                self.battle_sound_obj = mixer.Sound(self.battle_sound_path)
            else:
                messagebox.showwarning("音频文件缺失", f"未找到音频文件: {self.battle_sound_path}")
            
            if os.path.exists(self.fail_sound_path):
                self.fail_sound_obj = mixer.Sound(self.fail_sound_path)
            else:
                messagebox.showwarning("音频文件缺失", f"未找到音频文件: {self.fail_sound_path}")
        except Exception as e:
            messagebox.showerror("音频加载错误", f"加载音频文件时发生错误: {e}\n请确保音频文件是有效的MP3格式。")

    def play_sound_async(self, sound_obj):
        def play_thread_func(): # 更改函数名以避免混淆
            if sound_obj:
                try:
                    sound_obj.play()
                    # Pygame播放是异步的，无需等待
                except Exception as e:
                    print(f"播放音频时发生错误: {e}")
            
        threading.Thread(target=play_thread_func).start() # 调用新的函数名

    def _create_widgets(self):
        # 导入数据区域
        import_frame = tk.LabelFrame(self, text="数据导入")
        import_frame.pack(pady=10, padx=10, fill="x")

        import_button = tk.Button(import_frame, text="导入Excel数据", command=self._import_excel_data)
        import_button.pack(pady=5, padx=5)

        # 单据号加载区域
        bill_no_frame = tk.LabelFrame(self, text="单据号查询")
        bill_no_frame.pack(pady=10, padx=10, fill="x")

        tk.Label(bill_no_frame, text="单据号:").pack(side="left", padx=5)
        bill_no_entry = tk.Entry(bill_no_frame, textvariable=self.current_bill_no, width=30)
        bill_no_entry.pack(side="left", padx=5, expand=True, fill="x")
        load_button = tk.Button(bill_no_frame, text="加载", command=self._load_data)
        load_button.pack(side="left", padx=5)

        # 列表显示区域
        lists_frame = tk.Frame(self)
        lists_frame.pack(pady=10, padx=10, fill="both", expand=True)

        # 未核对列表
        unverified_frame = tk.LabelFrame(lists_frame, text="未核对列表")
        unverified_frame.pack(side="left", padx=5, fill="both", expand=True)
        
        self.unverified_count_label = tk.Label(unverified_frame, text="总行数: 0")
        self.unverified_count_label.pack(pady=2)

        self.unverified_listbox = tk.Listbox(unverified_frame)
        self.unverified_listbox.pack(fill="both", expand=True)

        # 已核对列表
        verified_frame = tk.LabelFrame(lists_frame, text="已核对列表")
        verified_frame.pack(side="right", padx=5, fill="both", expand=True)
        
        self.verified_count_label = tk.Label(verified_frame, text="总行数: 0")
        self.verified_count_label.pack(pady=2)

        self.verified_listbox = tk.Listbox(verified_frame)
        self.verified_listbox.pack(fill="both", expand=True)

        # 箱单号核对区域
        box_list_no_frame = tk.LabelFrame(self, text="箱单号核对")
        box_list_no_frame.pack(pady=10, padx=10, fill="x")

        tk.Label(box_list_no_frame, text="箱单号:").pack(side="left", padx=5)
        box_list_no_entry = tk.Entry(box_list_no_frame, textvariable=self.current_box_list_no_input, width=30)
        box_list_no_entry.pack(side="left", padx=5, expand=True, fill="x")
        box_list_no_entry.bind("<Return>", self._verify_box_list_no) # 绑定回车键

        # 删除单据号区域
        delete_bill_no_frame = tk.LabelFrame(self, text="删除单据号")
        delete_bill_no_frame.pack(pady=10, padx=10, fill="x")

        tk.Label(delete_bill_no_frame, text="单据号:").pack(side="left", padx=5)
        delete_bill_no_entry = tk.Entry(delete_bill_no_frame, textvariable=self.delete_bill_no_input, width=30)
        delete_bill_no_entry.pack(side="left", padx=5, expand=True, fill="x")
        delete_button = tk.Button(delete_bill_no_frame, text="删除", command=self._delete_bill_no_data)
        delete_button.pack(side="left", padx=5)

    def _import_excel_data(self):
        file_path = filedialog.askopenfilename(
            filetypes=[("Excel files", "*.xlsx *.xls")]
        )
        if not file_path:
            return

        try:
            df = (
                pl.read_excel(file_path, sheet_name="Sheet1",
                              read_options={'use_columns': ['单据号', '箱单号']},
                              schema_overrides={"单据号": pl.String, "箱单号": pl.String},
                              engine="calamine")
                .filter((~pl.col('单据号').is_null()) & (~pl.col('箱单号').is_null()))
                .unique(['箱单号','单据号'])
            )
            
            # 准备数据，Polars DataFrame可以直接转换为列表的列表或元组的列表
            data_to_insert = df.select([pl.col("单据号"), pl.col("箱单号")]).rows()
            
            self.db.insert_verify_status(data_to_insert)
            messagebox.showinfo("导入成功", f"成功导入 {len(data_to_insert)} 条数据到数据库。")
            self._load_data() # 导入后刷新当前加载的单据号数据
        except Exception as e:
            messagebox.showerror("导入失败", f"导入Excel数据时发生错误: {e}")

    def _load_data(self):
        bill_no = self.current_bill_no.get().strip()
        if not bill_no:
            messagebox.showwarning("输入错误", "请输入单据号。")
            self.play_sound_async(self.fail_sound_obj)
            return

        try:
            # 查询所有与当前单据号相关的箱单号
            sql = "SELECT box_list_no, is_verified FROM verify_status WHERE bill_no = :value"
            df = self.db.query(sql, bill_no)

            self.unverified_listbox.delete(0, tk.END)
            self.verified_listbox.delete(0, tk.END)

            if df.is_empty():
                messagebox.showinfo("查询结果", f"未找到单据号 '{bill_no}' 相关的箱单号。")
                # 更新行数标签
                self.unverified_count_label.config(text=f"总行数: 0")
                self.verified_count_label.config(text=f"总行数: 0")
                return

            unverified_count = 0
            verified_count = 0

            for row in df.iter_rows(named=True):
                box_list_no = row["box_list_no"]
                is_verified = row["is_verified"]
                if is_verified == 0:
                    self.unverified_listbox.insert(tk.END, box_list_no)
                    unverified_count += 1
                else:
                    self.verified_listbox.insert(tk.END, box_list_no)
                    verified_count += 1
            
            # 更新行数标签
            self.unverified_count_label.config(text=f"总行数: {unverified_count}")
            self.verified_count_label.config(text=f"总行数: {verified_count}")

        except Exception as e:
            messagebox.showerror("加载失败", f"加载数据时发生错误: {e}")

    def _verify_box_list_no(self, event=None):
        bill_no = self.current_bill_no.get().strip()
        box_list_no_to_verify = self.current_box_list_no_input.get().strip()

        if not bill_no:
            messagebox.showwarning("输入错误", "请先输入并加载单据号。")
            self.play_sound_async(self.fail_sound_obj)
            return
        if not box_list_no_to_verify:
            # messagebox.showwarning("输入错误", "请输入或扫描箱单号。")
            # 不弹出警告框，只清空输入并播放失败音效
            self.current_box_list_no_input.set("")
            self.play_sound_async(self.fail_sound_obj)
            return

        try:
            sql = "SELECT is_verified FROM verify_status WHERE bill_no = ? AND box_list_no = ?"
            with self.db.connect() as conn:
                cursor = conn.cursor()
                cursor.execute(sql, (bill_no, box_list_no_to_verify))
                result = cursor.fetchone()

            self.current_box_list_no_input.set("") # 清空输入框

            if result:
                is_verified = result[0]
                if is_verified == 0:
                    # 未核对，进行更新
                    self.db.update_verify_status(bill_no, box_list_no_to_verify, 1)
                    self.play_sound_async(self.battle_sound_obj)
                    self._load_data() # 刷新列表
                else:
                    # 已核对
                    self.play_sound_async(self.fail_sound_obj)
                    messagebox.showwarning("已核对", f"箱单号 '{box_list_no_to_verify}' 已经核对过。")
            else:
                # 不属于当前单据号
                self.play_sound_async(self.fail_sound_obj)
                messagebox.showerror("核对失败", f"箱单号 '{box_list_no_to_verify}' 不属于当前单据号 '{bill_no}' 或不存在。")

        except Exception as e:
            messagebox.showerror("核对错误", f"核对箱单号时发生错误: {e}")
            self.current_box_list_no_input.set("")
            self.play_sound_async(self.fail_sound_obj)

    def _delete_bill_no_data(self):
        bill_no_to_delete = self.delete_bill_no_input.get().strip()
        if not bill_no_to_delete:
            messagebox.showwarning("输入错误", "请输入要删除的单据号。")
            return

        if not messagebox.askyesno("确认删除", f"您确定要删除单据号 '{bill_no_to_delete}' 的所有数据吗？此操作不可逆！"):
            return

        try:
            rows_deleted = self.db.delete_verify_status_by_bill_no(bill_no_to_delete)
            messagebox.showinfo("删除成功", f"成功删除单据号 '{bill_no_to_delete}' 的 {rows_deleted} 行数据。")
            self.delete_bill_no_input.set("") # 清空删除文本框

            # 如果当前加载的单据号是刚删除的，则清空列表
            if self.current_bill_no.get().strip() == bill_no_to_delete:
                self.current_bill_no.set("") # 清空当前单据号输入框
                self.unverified_listbox.delete(0, tk.END)
                self.verified_listbox.delete(0, tk.END)
                self.unverified_count_label.config(text=f"总行数: 0")
                self.verified_count_label.config(text=f"总行数: 0")

        except Exception as e:
            messagebox.showerror("删除失败", f"删除数据时发生错误: {e}")


if __name__ == "__main__":
    # 确保数据库文件存在
    DB_FILE = r'\\10.146.38.191\最新-备件验收\海外原件复核程序\invoice_verification.db'
    if not os.path.exists(DB_FILE):
        # 如果文件不存在，则创建父目录
        os.makedirs(os.path.dirname(DB_FILE), exist_ok=True)
        # SQLite类会在init中创建表，所以这里只需要确保文件存在
        # 可以选择在这里初始化一次SQLite类来创建表，确保文件被创建
        temp_db = SQLite(DB_FILE)
        del temp_db # 确保数据库连接关闭
        print(f"数据库文件 {DB_FILE} 已创建。")
    
    # 提示用户安装pygame
    try:
        import pygame.mixer as mixer_check
        if not mixer_check.get_init():
            mixer_check.init()
    except ImportError:
        print("pygame 库未安装。请运行 'pip install pygame' 进行安装。")
        messagebox.showwarning("缺失依赖", "pygame 库未安装。请运行 'pip install pygame' 进行安装。")
    except Exception as e:
        print(f"Pygame Mixer 初始化失败：{e}")
        messagebox.showwarning("音频初始化错误", "无法初始化Pygame音频混音器。错误：" + str(e))

    app = InvoiceVerificationApp(DB_FILE)
    app.mainloop()
